package com.dudu.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.bean.IfControlBean;
import com.dudu.bean.IfControlCheckConclusionPushBean;
import com.dudu.bean.IfControlRuleBean;
import com.dudu.utils.DateUtils;

import core.CoreDao;
import core.CoreDaoInvoke;
import core.db.DBConnectionUtil;
import net.sf.json.JSONObject;

/**
 * 信控数据库操作类
 */
public class InfoControlDao {

	private static CoreDao coreDao = CoreDaoInvoke.getInstance();

	// 日志输出
	private static final Logger log = Logger.getLogger(InfoControlDao.class.getSimpleName());

	/**
	 * 新增信控记录
	 * 
	 * @param requestBean
	 * @param rule
	 * @return
	 */
	public int startInfoControl(IfControlBean requestBean, IfControlRuleBean rule) {
		// 数据库连接
		Connection con = null;
		// 最终返回结果
		int result = -1;

		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer(
					"insert into t_infocontrol(rule_id,user_identify,app_key,cust_account,batch_number,checkers,create_time,status,ability_type,product_code) values(?,?,?,?,?,?,sysdate(),?,?,?)");
			// 查询获取结果
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(),
					new Object[] { rule.getId(), requestBean.getUserIdentify(), requestBean.getAppKey(),
							requestBean.getCustAccount(), requestBean.getBatchNumber(), rule.getCheckNumbers(), "0",
							requestBean.getAbilityType(), requestBean.getProductCode() });
			// 获取插入的结果
			JSONObject obj = list.get(0);
			result = obj.optInt("result");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 创建信控审核结果推送记录
	 * 
	 * @param bean
	 * @return
	 */
	public int addIfControlCheckConclusionPushInfo(IfControlCheckConclusionPushBean bean) {
		// 数据库连接
		Connection con = null;
		// 最终返回结果
		int result = -1;
		List params = new ArrayList(15);
		String time = DateUtils.getCurrentTime();
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer(
					"insert into t_infocontrol_pushcheck(history_id,push_url,batch_number,status,checkconclusion,push_count,create_time,plan_pushtime) ");
			String url = bean.getPush_url();
			String[] urls = url.split(",");
			sql.append("values");
			for (int i = 0, l = urls.length; i < l; i++) {
				if (i > 0)
					sql.append(",");
				sql.append("(?,?,?,?,?,?,?,?)");
				params.add(bean.getHistory_id());
				params.add(urls[i]);
				params.add(bean.getBatch_number());
				params.add(bean.getStatus());
				params.add(bean.getCheckconclusion());
				params.add(bean.getPush_count());
				params.add(time);
				params.add(time);
			}
			// 查询获取结果
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), params.toArray());
			// 获取插入的结果
			JSONObject obj = list.get(0);
			result = obj.optInt("result");
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("创建信控审核结果推送记录异常", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 根据批次号等信息查询已发送的语音通知数量
	 * 
	 * @param ifControl
	 * @return
	 */
	public int querySendNoticeCountByBatchNumber(IfControlBean ifControl, String status) {
		int count = -1;
		// 数据库连接
		Connection con = null;

		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer(
					"select count(nt.id) ct from t_send_notice nt,t_meet_info meet where nt.app_key=? and nt.cust_account=? ");
			sql.append(
					"and nt.batch_number=? and nt.status=? and meet.id=nt.meet_ims_id and meet.user_identify=? and nt.voice_type=?");
			// 查询获取结果
			List<JSONObject> list = coreDao
					.serviceJsonSql(con, sql.toString(),
							new Object[] { ifControl.getAppKey(), ifControl.getCustAccount(),
									ifControl.getBatchNumber(), status, ifControl.getUserIdentify(),
									ifControl.getAbilityType() });
			// 类型转换
			if (list != null && list.size() > 0) {
				count = list.get(0).getInt("ct");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("根据批次号查询语音发送量失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return count;
	}

	/**
	 * 根据账户信息查询信控监控数据
	 * 
	 * @param ifControl
	 * @return
	 */
	public IfControlBean queryIfControlByAccountInfo(IfControlBean ifControl) {
		IfControlBean ifBean = null;
		// 数据库连接
		Connection con = null;

		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer(
					"select ifc.id,ifc.rule_id,r.check_period,ifc.checkers from t_infocontrol ifc , t_infocontrol_rules r where ifc.user_identify=? and ifc.app_key=? and ifc.cust_account=? and ifc.batch_number=? and ifc.status=? and r.id=ifc.rule_id");
			// 查询获取结果
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(),
					new Object[] { ifControl.getUserIdentify(), ifControl.getAppKey(), ifControl.getCustAccount(),
							ifControl.getBatchNumber(), ifControl.getStatus() });
			// 类型转换
			if (list != null && list.size() > 0) {
				JSONObject ifControlInfo = list.get(0);
				ifBean = new IfControlBean();
				ifBean.setId(ifControlInfo.getInt("id"));
				IfControlRuleBean rule = new IfControlRuleBean();
				rule.setId(ifControlInfo.getInt("rule_id"));
				rule.setCheckPeriod(ifControlInfo.getInt("check_period"));
				rule.setCheckNumbers(ifControlInfo.getString("checkers"));
				ifBean.setRule(rule);
			}
		} catch (Exception ex) {
			log.error("获取信控信息失败", ex);
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return ifBean;
	}

	/**
	 * 根据应用标识和计费账户信息查询信控审核结果推送地址
	 * 
	 * @param custAccount
	 *            计费账户
	 * @param appKey
	 *            应用账户
	 * @return
	 */
	public String queryIfControlUrlByAccountInfo(String custAccount, String appKey) {
		String url = null;
		// 数据库连接
		Connection con = null;

		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer("select url_infocontrol from t_account_extend where cust_account=? ");
			sql.append("union ");
			sql.append("select url_infocontrol from t_app_extend where app_key=? ");
			// 查询获取结果
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { custAccount, appKey });
			// 类型转换
			if (list != null && list.size() > 0) {
				JSONObject urlInfo = list.get(0);
				url = urlInfo.getString("url_infocontrol");
			}
		} catch (Exception ex) {
			log.error("获取信控推送地址失败", ex);
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return url;
	}

	/**
	 * 根据主键查询信控监控数据
	 * 
	 * @param ifControl
	 * @return
	 */
	public IfControlBean queryIfControlById(IfControlBean ifControl) {
		IfControlBean ifBean = null;
		// 数据库连接
		Connection con = null;

		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写sql
			StringBuffer sql = new StringBuffer(
					"select ifc.id,ifc.rule_id,r.check_period,ifc.checkers,ifc.batch_number,ifc.ability_type,ifc.app_key,ifc.cust_account,ifc.product_code from t_infocontrol ifc , t_infocontrol_rules r where ifc.id=? and ifc.rule_id=r.id");
			// 查询获取结果
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { ifControl.getId() });
			// 类型转换
			if (list != null && list.size() > 0) {
				JSONObject ifControlInfo = list.get(0);
				ifBean = new IfControlBean();
				ifBean.setId(ifControlInfo.getInt("id"));
				ifBean.setBatchNumber(ifControlInfo.getString("batch_number"));
				ifBean.setAbilityType(ifControlInfo.getString("ability_type"));
				ifBean.setProductCode(ifControlInfo.getString("product_code"));
				ifBean.setAppKey(ifControlInfo.getString("app_key"));
				ifBean.setCustAccount(ifControlInfo.getString("cust_account"));
				IfControlRuleBean rule = new IfControlRuleBean();
				rule.setId(ifControlInfo.getInt("rule_id"));
				rule.setCheckPeriod(ifControlInfo.getInt("check_period"));
				rule.setCheckNumbers(ifControlInfo.getString("checkers"));
				ifBean.setRule(rule);
			}
		} catch (Exception ex) {
			log.error("获取信控信息失败", ex);
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return ifBean;
	}

	/**
	 * 更新语音通知的状态和发送时间
	 * 
	 * @param ifControl
	 * @param status
	 * @param time
	 * @return
	 */
	public int updateNoticeStatusAndScheduletime(IfControlBean ifControl, String status, String time) {
		// 数据库连接
		Connection con = null;
		int result = -1;
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写SQL
			StringBuffer sql = new StringBuffer(
					" update t_send_notice set status=?,schedule_send_time=? where app_key=? and cust_account=? and batch_number=? and exists(select 1 from t_meet_info meet where meet.id=t_send_notice.meet_ims_id and meet.user_identify=?) and status='-1' and voice_type=?");
			// 执行sql，更新相应数据
			List<JSONObject> list = coreDao
					.serviceJsonSql(con, sql.toString(),
							new Object[] { status, time, ifControl.getAppKey(), ifControl.getCustAccount(),
									ifControl.getBatchNumber(), ifControl.getUserIdentify(),
									ifControl.getAbilityType() });
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("更新信息失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 信控记录转历史
	 * 
	 * @param ifControl
	 * @return
	 */
	public boolean finishIfControlProcess(IfControlBean ifControl) {
		Connection con = null;
		boolean finish = false;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuilder sql = new StringBuilder();
			sql.append(
					"insert into t_infocontrol_history(rule_id,user_identify,app_key,cust_account,batch_number,checkers,create_time,checker,checkconclusion,check_time,status,ability_type,product_code) ");
			sql.append(
					"select rule_id,user_identify,app_key,cust_account,batch_number,checkers,create_time,?,?,SYSDATE(),?,ability_type,product_code from t_infocontrol ");
			sql.append("where id=?");
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { ifControl.getChecker(),
					ifControl.getCheckConclusion(), ifControl.getStatus(), ifControl.getId() });
			if (list != null && list.size() > 0) {
				int history_id = list.get(0).getInt("result");
				String delSql = " delete from t_infocontrol where id =? ";
				// 执行sql
				list = coreDao.serviceJsonSql(con, delSql, new Object[] { ifControl.getId() });
				if (list != null && list.size() > 0) {
					finish = true;
					ifControl.setHistroy_id(history_id);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("信控流程结束失败", ex);
			try {
				if (con != null)
					con.rollback();
			} catch (SQLException e) {
			}
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return finish;
	}

	/**
	 * 查询信控核查信息待推送记录
	 * 
	 * @return
	 */
	public List<JSONObject> queryInfoControlPushInfo() {
		List<JSONObject> result = null;
		// 数据库连接
		Connection con = null;
		String time = DateUtils.getCurrentTime();
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			String sql = "select id,push_url,batch_number,checkconclusion,push_count from t_infocontrol_pushcheck where  plan_pushtime<? and (status='0' or (status='2' and push_count<3)) order by push_time limit 50";
			// 查询获取结果
			result = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { time });
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("查询信控核查推送记录失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 根据主键更新信控核查推送数据的状态
	 * 
	 * @param status
	 * @param ids
	 * @return
	 */
	public int updateInfoControlPushInfoStatus(String status, String ids) {
		// 数据库连接
		Connection con = null;
		int result = -1;
		List params = new ArrayList();
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写SQL
			StringBuffer sql = new StringBuffer(" update t_infocontrol_pushcheck set status=? where id in(");
			String[] primaryKeys = ids.split(",");
			params.add(status);
			for (int i = 0; i < primaryKeys.length; i++) {
				if (i > 0)
					sql.append(",");
				sql.append("?");
				params.add(primaryKeys[i]);
			}
			sql.append(")");
			// 执行sql，更新相应数据
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), params.toArray());
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("更新信息失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 更新信控核查推送结果 包含状态、推送时间、推送结果信息
	 * 
	 * @param bean
	 * @return
	 */
	public int updateInfoControlPushInfo(IfControlCheckConclusionPushBean bean) {
		// 数据库连接
		Connection con = null;
		int result = -1;
		List params = new ArrayList();
		String time = DateUtils.getCurrentTime();
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写SQL
			StringBuffer sql = new StringBuffer(
					" update t_infocontrol_pushcheck set message=?,status=?,push_count=?,push_time=? ");
			params.add(bean.getMessage());
			params.add(bean.getStatus());
			params.add(bean.getPush_count());
			params.add(time);
			if (StringUtils.isNotEmpty(bean.getPlan_pushtime())) {
				sql.append(",plan_pushtime=? ");
				params.add(bean.getPlan_pushtime());
			}
			params.add(bean.getId());
			sql.append("where id=?");
			// 执行sql，更新相应数据
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), params.toArray());
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("更新信息失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 取消语音通知的发送，更新状态为3
	 * 
	 * @param bean
	 * @return
	 */
	public int cancelSendNotice(IfControlBean bean) {
		// 数据库连接
		Connection con = null;
		int result = -1;
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写SQL
			String sql = " update t_send_notice set status=? where app_key=? and cust_account=? and batch_number=? and status='2' and voice_type=?";
			// 执行sql，更新相应数据
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { "3", bean.getAppKey(),
					bean.getCustAccount(), bean.getBatchNumber(), bean.getAbilityType() });
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("更新信息失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public List<JSONObject> queryInfoControlCheckOverTimeList() {
		List<JSONObject> result = null;
		// 数据库连接
		Connection con = null;
		String time = DateUtils.getCurrentTime();
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuilder sql = new StringBuilder();
			sql.append(
					"SELECT DISTINCT(ifc.id) id,ifc.app_key,ifc.batch_number,ifc.ability_type,ifc.cust_account FROM ");
			sql.append("t_send_notice t,t_infocontrol ifc WHERE t. STATUS = '2' ");
			sql.append("AND (t.run_thread IS NULL OR t.run_thread = '') AND t.schedule_send_time < '").append(time)
					.append("' ");
			sql.append(
					"AND ifc.app_key = t.app_key AND ifc.batch_number = t.batch_number AND ifc.cust_account = t.cust_account ");
			sql.append("AND ifc. STATUS = '0' AND ifc.ability_type = t.voice_type LIMIT 100 ");
			// 查询获取结果
			result = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { null });
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("查询信控核查推送记录失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public int updateVoiceStatus(IfControlBean bean) {
		// 数据库连接
		Connection con = null;
		int result = -1;
		try {
			// 获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			// 拼写SQL
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE t_send_notice SET STATUS = '0' WHERE STATUS = '2' ");
			sql.append(
					"AND (run_thread IS NULL OR run_thread = '') and app_key=? and cust_account=? and batch_number=? and voice_type=? ");
			// 执行sql，更新相应数据
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(), new Object[] { bean.getAppKey(),
					bean.getCustAccount(), bean.getBatchNumber(), bean.getAbilityType() });
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error("更新信息失败", ex);
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

}
